package com.attendance.www.hr.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateUtils;

import com.attendance.www.hr.beans.AttendanceBean;
import com.attendance.www.hr.entitys.CheckinoutMorning;


public class AccessUtils {
	//获取连接
	public static String url="jdbc:ucanaccess://";
	//读取地址失败的key
	public static String failedKey="FK";
		
	//获取access数据库数据
	public static Map<String, AttendanceBean> getAccessInfo(String urlPart, Date startDate) {
		
		//保存考勤map集合
		Map<String, AttendanceBean> attMap=new HashMap<>();
		
		try {
			Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动失败");
		}
		
		String[] strs=urlPart.split(",");
		String address = null;
		String errorMessage = "";
		for(int i=0,len=strs.length;i<len;i++){
		    Connection conn=null;
			Boolean hasConn=true;
			try {
				conn = DriverManager.getConnection(url + strs[i].toString());
			} catch (Exception ex) {
				hasConn = false;//地址失效时
				ex.printStackTrace();
				errorMessage=errorMessage + ex.getMessage() + ";";
			}
			//建立连接后，查询集合
			if (hasConn) {
				Statement stat=null;
				ResultSet rs=null;
				attMap = getResultSetAttendanceMap(attMap, rs, strs[i].toString(), conn, stat, startDate);
			} else {//地址失效时
				if (address != null) {
					address = address + strs[i].toString() + ";";
				} else {
					address = strs[i].toString() + ";";
				}
			}
		}
		if (address != null) {
			AttendanceBean att=new AttendanceBean();
			att.setAddress(address);
			att.setErrorMessage(errorMessage);
			attMap.put(failedKey, att);
		}
		return attMap;
	}
	
	//处理ResultSet的方法
	private static Map<String, AttendanceBean> getResultSetAttendanceMap(Map<String, AttendanceBean> attMap, ResultSet rs,
			String urlPart,Connection conn, Statement stat, Date startDate){
		try {
			//考勤时间，昨天6点
			Calendar calStartDate = Calendar.getInstance();
			/*calStartDate.setTime(DateUtils.addDays(new Date(), -1));*/
			calStartDate.setTime(startDate);
			calStartDate.set(Calendar.HOUR_OF_DAY,6);
			calStartDate.set(Calendar.MINUTE,0);
			calStartDate.set(Calendar.SECOND,0);
			calStartDate.set(Calendar.MILLISECOND,0);
			startDate=calStartDate.getTime();
			Date endDate=DateUtils.addDays(startDate, 1);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String startDateStr="#" + sdf.format(startDate) + "#";
			String endDateStr="#" + sdf.format(endDate) + "#";
			
			String sql = "Select ch.userid, ch.checktime, ufo.badgenumber From checkinout ch "
					+"left join userinfo ufo on ufo.userid = ch.userid "
					+"where ch.checktime > " + startDateStr 
					+"and ch.checktime <= " + endDateStr
					+"order by ch.userid ,ch.checktime asc";
			//+"and ufo.badgenumber not like '50___' "
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			attMap = getAttendanceMap(attMap, rs, startDate, urlPart);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//关闭资源
		try {
			if (rs!=null) {
				rs.close();
			}
			if (stat!=null) {
				stat.close();
			}
			if (conn!=null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return attMap;
	}
	
	//考勤数据循环方法
	private static Map<String, AttendanceBean> getAttendanceMap(Map<String, AttendanceBean> attMap, ResultSet rs, Date startDate, String urlPart){
		AttendanceBean attendanceBean=new AttendanceBean();
		try {
			String nightTime = "06:00";
			while (rs.next()) {
				String attTime=rs.getTime("CHECKTIME").toString().substring(0, 5);
				String employeeNo=rs.getString("badgenumber");
				if (employeeNo.length() == 4) {//员工号在打卡机里是4位的，补0处理
					employeeNo = "0" + employeeNo;
				}
				if (attMap.get(employeeNo) == null) {
					attendanceBean=new AttendanceBean();
					attendanceBean.setAttendanceDate(startDate);
					attendanceBean.setEmployeeNo(employeeNo);
					attendanceBean.setStartTime(attTime);
					attendanceBean.setEndTime(attTime);
					attendanceBean.setAddress(urlPart);
					attMap.put(employeeNo, attendanceBean);
				} else {//同一个的考勤
					attendanceBean = attMap.get(employeeNo);
					//和早晚时间比较
					String startTime = attendanceBean.getStartTime();
					String endTime = attendanceBean.getEndTime();
					if (TimeUtils.compareTime(attTime, nightTime) < 0) {//凌晨打卡处理
						attendanceBean.setEndTime(attTime);
						attMap.put(employeeNo, attendanceBean);
					} else {
						if (TimeUtils.compareTime(attTime, endTime) > 0) {//晚上打卡时间
							attendanceBean.setEndTime(attTime);
							attMap.put(employeeNo, attendanceBean);
						} else if (TimeUtils.compareTime(attTime, startTime) < 0) {//早上打卡时间
							attendanceBean.setStartTime(attTime);
							attMap.put(employeeNo, attendanceBean);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return attMap;
	}
	
	/**
	 * 获取access数据库中早上0点-8点30打卡信息
	 * @param urlPart 考勤打卡数据地址
	 * @return
	 */
	public static List<CheckinoutMorning> getAccessInfoMorning(String urlPart, Date startDate) {
		//保存考勤List集合
		List<CheckinoutMorning> cmList = new ArrayList<>();
		try {
			Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动失败");
		}
		String[] strs=urlPart.split(",");
		String errorMessage = "";
		for(int i=0,len=strs.length;i<len;i++){
		    Connection conn=null;
			Boolean hasConn=true;
			try {
				conn = DriverManager.getConnection(url + strs[i].toString());
			} catch (Exception ex) {
				hasConn = false;//地址失效时
				ex.printStackTrace();
				errorMessage=errorMessage + ex.getMessage() + ";";
			}
			//建立连接后，查询集合
			if (hasConn) {
				Statement stat=null;
				ResultSet rs=null;
				//TODO
				try {
					//打卡时间，昨天0点-8点30
					Calendar calStartDate = Calendar.getInstance();
					/*calStartDate.setTime(DateUtils.addDays(new Date(), -1));
					calStartDate.set(Calendar.HOUR_OF_DAY,0);
					calStartDate.set(Calendar.MINUTE,0);
					calStartDate.set(Calendar.SECOND,0);
					calStartDate.set(Calendar.MILLISECOND,0);*/
//					Date startDate=calStartDate.getTime();
					calStartDate.setTime(startDate);
					calStartDate.set(Calendar.HOUR_OF_DAY,8);
					calStartDate.set(Calendar.MINUTE,30);
					Date endDate=calStartDate.getTime();
					
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					String startDateStr="#" + sdf.format(startDate) + "#";
					String endDateStr="#" + sdf.format(endDate) + "#";
					
					String sql = "Select ch.userid, ch.checktime, ufo.badgenumber From checkinout ch "
							+"left join userinfo ufo on ufo.userid = ch.userid "
							+"where ch.checktime > " + startDateStr 
							+"and ch.checktime < " + endDateStr
							+"order by ch.userid ,ch.checktime asc";
					stat = conn.createStatement();
					rs = stat.executeQuery(sql);
					//TODO 循环
					CheckinoutMorning checkinoutMorning=new CheckinoutMorning();
					try {
						while (rs.next()) {
							String attTime=rs.getTime("CHECKTIME").toString().substring(0, 5);
							String employeeNo=rs.getString("badgenumber");
							if (employeeNo.length() == 4) {//员工号在打卡机里是4位的，补0处理
								employeeNo = "0" + employeeNo;
							}
							checkinoutMorning=new CheckinoutMorning();
							checkinoutMorning.setAttendanceDate(startDate);
							checkinoutMorning.setEmployeeNo(employeeNo);
							checkinoutMorning.setMorningTime(attTime);
							cmList.add(checkinoutMorning);
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				//关闭资源
				try {
					if (rs!=null) {
						rs.close();
					}
					if (stat!=null) {
						stat.close();
					}
					if (conn!=null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} else {//地址失效时
			}
		}
		return cmList;
	}
	
	
}
